using MediatR;
using Dapper;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using RBAC.Domain.ProductionPlans;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ProductionPlans;
using System.Text;
using System.Collections.Generic;
using System;
using System.IO;

namespace RBAC.Read.Api.Application.Handler.ProductionPlans
{
    public class ExportProductionPlanCommandHandler : IRequestHandler<ExportProductionPlanCommands, byte[]>
    {
        private readonly IBaseRepository<ProductionPlan> _planRepo;

        public ExportProductionPlanCommandHandler(IBaseRepository<ProductionPlan> planRepo)
        {
            _planRepo = planRepo;
        }

        public Task<byte[]> Handle(ExportProductionPlanCommands request, CancellationToken cancellationToken)
        {
            // 1. 查询数据（与列表查询保持一致的筛选与列）
            var sbWhere = new StringBuilder("p.IsDeleted = 0");
            var param = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(request.PlanCode))
            {
                sbWhere.Append(" AND p.PlanCode LIKE @PlanCode");
                param.Add("PlanCode", $"%{request.PlanCode}%");
            }
            if (!string.IsNullOrWhiteSpace(request.PlanTopic))
            {
                sbWhere.Append(" AND p.PlanTopic LIKE @PlanTopic");
                param.Add("PlanTopic", $"%{request.PlanTopic}%");
            }

            var where = sbWhere.ToString();

            var dataSql = $@"
                            SELECT 
                                p.Id,
                                p.PlanCode,
                                p.PlanTopic,
                                p.DocumentsTime,
                                TRIM(CONCAT_WS(' ', COALESCE(g.GoodName, ''), COALESCE(g.SpecificationModel, ''), COALESCE(g.Unit, ''))) AS GoodOverview,
                                p.AssociatedProjects,
                                p.Remark
                            FROM ProductionPlan p
                            LEFT JOIN GoodsInfo g ON g.Id = p.GoodsId AND g.IsDeleted = 0
                            WHERE {where}
                            ORDER BY p.Id DESC";

            var rows = _planRepo.Query<dynamic>(dataSql, param);

            // 2. 生成 Excel
            var workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet("生产计划");

            var headerStyle = workbook.CreateCellStyle();
            var headerFont = workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            var headers = new[]
            {
                "序号","计划单号","计划主题","单据日期","物品概述","关联项目","备注"
            };

            var headerRow = sheet.CreateRow(0);
            for (int i = 0; i < headers.Length; i++)
            {
                var cell = headerRow.CreateCell(i);
                cell.SetCellValue(headers[i]);
                cell.CellStyle = headerStyle;
            }

            int rowIndex = 1;
            int serial = 0;
            foreach (var r in rows)
            {
                var row = sheet.CreateRow(rowIndex++);
                row.CreateCell(0).SetCellValue(++serial);
                row.CreateCell(1).SetCellValue((string?)r.PlanCode ?? "");
                row.CreateCell(2).SetCellValue((string?)r.PlanTopic ?? "");
                string docTime = (r.DocumentsTime == null || r.DocumentsTime == default(DateTime)) ? string.Empty : ((DateTime)r.DocumentsTime).ToString("yyyy-MM-dd");
                row.CreateCell(3).SetCellValue(docTime);
                row.CreateCell(4).SetCellValue((string?)r.GoodOverview ?? "");
                row.CreateCell(5).SetCellValue((string?)r.AssociatedProjects ?? "");
                row.CreateCell(6).SetCellValue((string?)r.Remark ?? "");
            }

            for (int i = 0; i < headers.Length; i++)
                sheet.AutoSizeColumn(i);

            using var ms = new MemoryStream();
            workbook.Write(ms);
            var bytes = ms.ToArray();
            return Task.FromResult(bytes);
        }
    }
}
